/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package oes;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import javax.swing.JFrame;
import javax.swing.JOptionPane;

public class DBConnector {

    private JFrame frame;
    // Variables for managing the database connection.
    private boolean connected;
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    // Variables for storing database settings.
    private String address;
    private String port;
    private String dbname;
    private String uname;
    private String pass;

    // Constructor.
    public DBConnector(JFrame frame) {
        this.frame = frame;
        connected = false;
        conn = null;
        stmt = null;
        rs = null;
        address = "";
        port = "";
        dbname = "";
        uname = "";
        pass = "";
    }

    // Connect to the database using the current settings.
    public boolean connect() {
        boolean result;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://" + address + ":" + port + "/" + dbname, uname, pass);
            stmt = conn.createStatement();
            result = true;
            connected = true;
            JOptionPane.showMessageDialog(frame, "Connected to database.", "Info", 1);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(frame, "Could not connect to database: " + e.getMessage(), "Error!", 0);
            result = false;
        }
        return result;
    }

    // Disconnect from the database.
    public void disconnect() {
        try {
            stmt.close();
            conn.close();
            connected = false;
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "Could not disconnect from database: " + e.getMessage(), "Error!", 0);
        }
    }

    public void insertLesson(String date, int ClassID, int RoomID) {
        String sql = "insert into Lesson values(0,\""+date+"\","+ClassID+","+RoomID+")";
        System.out.println("INSERT STMT: "+sql);
        try {
            stmt.execute(sql);
        } catch(SQLException e) {
            // Crap!
        }
    }
    
    // Get the Lesson for a Room on a specific date.
    public Lesson getRoomLesson(int r, String date) {
        Lesson l = null;
        String sql = "select * from Lesson where DateOfUse='" + date + "' and RoomID=" + r;
        System.out.println(sql);
        try {
            rs = stmt.executeQuery(sql);
            if( rs.next() ) {
                l = new Lesson(rs.getString("DateOfUse"), rs.getInt("ClassID"), rs.getInt("RoomID"));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "Could not find Classroom: " + e.getMessage(), "Error!", 0);
        }
        return l;
    }

    // Generate a ClassID list for a Student.
    public ArrayList<OESClass> getStudentClasses(int s) {
        ArrayList<OESClass> al = new ArrayList();
        String sql = "select * from StudentClass JOIN Class ON StudentClass.ClassID = Class.ClassID where StudentID='" + s +"'";
        System.out.println(sql);
        try {
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                OESClass cl = new OESClass(rs.getString("CourseName"), rs.getInt("ClassID"), rs.getInt("StaffID"));
                al.add(cl);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "Could not find Student: " + e.getMessage(), "Error!", 0);
        }
        return al;
    }

    public ArrayList<OESClass> getTeacherClasses(int s) {
        ArrayList<OESClass> al = new ArrayList();
        String sql = "select * from Class where StaffID=\""+s+"\"";
        try {
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                al.add(new OESClass(rs.getString("CourseName"), rs.getInt("ClassID"), rs.getInt("StaffID")));
            }
        } catch(SQLException e) {
            JOptionPane.showMessageDialog(frame, "getTeacherClass(): "+e.getMessage(), "Error", 0);
        }
        return al;
    }
    
    // Save database connection info to a file.
    public boolean saveSettings(String FileName) {
        boolean result = false;
        // If we are connected then disconnect, so that we can test the settings.
        if (connected) {
            disconnect();
        }
        // If the connection is successful the settings are saved.
        if (connect()) {
            try {
                Properties propertiesOutput = new Properties();
                propertiesOutput.setProperty("Address", address);
                propertiesOutput.setProperty("Port", port);
                propertiesOutput.setProperty("DBName", dbname);
                propertiesOutput.setProperty("UName", uname);
                propertiesOutput.setProperty("Pass", pass);
                propertiesOutput.store(new FileOutputStream(FileName + ".properties"), null);
                JOptionPane.showMessageDialog(frame, "Settings was saved.", "Info", 1);
                result = true;
            } catch (IOException e) {
                JOptionPane.showMessageDialog(frame, "Could not save settings: " + e.getMessage(), "Error!", 0);
                result = false;
            }
        }
        return result;
    }

    // Load database connection info from a file.
    public boolean loadSettings(String FileName) {
        boolean result;
        try {
            Properties propertiesInput = new Properties();
            propertiesInput.load(new FileInputStream(FileName + ".properties"));
            address = propertiesInput.getProperty("Address");
            port = propertiesInput.getProperty("Port");
            dbname = propertiesInput.getProperty("DBName");
            uname = propertiesInput.getProperty("UName");
            pass = propertiesInput.getProperty("Pass");
            result = true;
        } catch (IOException e) {
            JOptionPane.showMessageDialog(frame, "Could not load settings: " + e.getMessage(), "Info", 0);
            result = false;
        }
        return result;
    }

    // Get the Lesson for a specific Class on a specific date.
    public ArrayList<Lesson> getClassLesson(int c, String date) {
        ArrayList<Lesson> al = new ArrayList<Lesson>();
        String sql = "select * from lesson where ClassID='"+c+"' and DateOfUse='" + date + "'";
        
        try {
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                al.add(new Lesson(rs.getString("DateOfUse"), rs.getInt("ClassID"), rs.getInt("RoomID")));     
            }
            
        } catch(Exception ex) {
            System.out.println(ex.getMessage());
        }
        return al;
    }

    public ArrayList getClasses() {
        ArrayList al = new ArrayList();
        String sql = "select * from Class";
        try {
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                OESClass theClass = new OESClass(rs.getString("CourseName"), rs.getInt("ClassID"), rs.getInt("StaffID"));
                al.add(theClass);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "Could not find Class: " + e.getMessage(), "Error!", 0);
        }
        return al;
    }

    public String getLessonClassName(int oclass) {
        String s = null;
        String sql = "select CourseName from Class where ClassID='" + oclass + "'";
        try {
            rs = stmt.executeQuery(sql);
            rs.next();
            s = rs.getString("CourseName");
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "getLessonClassName(): "+e.getMessage(), "Error!", 0);
        }
        return s;
    }

    public String getLessonStaffName(int oclass) {
        String s = null;
        String sql = "select StaffID from Class where ClassID=\""+oclass+"\"";
        try {
            rs = stmt.executeQuery(sql);
            if(rs.next()) {
                int si = rs.getInt("StaffID");
                sql = "select Firstname,Lastname from Staff where CPR=\""+si+"\"";
                rs = stmt.executeQuery(sql);
                if(rs.next()) {
                    s = "" + rs.getString("Firstname") + " " + rs.getString("Lastname");
                }
            }
        } catch(SQLException e) {
            JOptionPane.showMessageDialog(frame, "getLessonStaffName(): "+e.getMessage(), "Error!", 0);
        }
        return s;
    }
    
    public ArrayList<Classroom> getAvailableRooms(int year, int month, int day, int hour, int l) {
        ArrayList<Classroom> al = new ArrayList();
        try {
            // Select all the ClassRooms that are not used in the specified period.
            String sql = "select r.* from classroom r left join lesson l on r.roomid=l.roomid and l.dateofuse>=\"" + year + "-" + month + "-" + day + " " + (hour) + ":00\" and l.dateofuse<=\"" + year + "-" + month + "-" + day + " " + (hour + (l-1)) + ":00\" where l.roomid is null order by PLACE,NR asc";
            System.out.println(sql);
            rs = stmt.executeQuery(sql);

            // Put all available Classrooms into the ArrayList.
            while (rs.next()) {
                Classroom cr = new Classroom(rs.getInt("RoomID"), rs.getString("NR"), rs.getString("Place"));
                al.add(cr);
                System.out.println("RoomID: "+rs.getInt("RoomID")+", NR: "+rs.getString("NR")+", Place: "+rs.getString("Place"));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(frame, "SQL Error: " + e.getMessage(), "Error!", 0);
        }
        return al;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public void setDBName(String dbname) {
        this.dbname = dbname;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

    public void setPort(String port) {
        this.port = port;
    }

    public void setUName(String uname) {
        this.uname = uname;
    }

    public String getAddress() {
        return address;
    }

    public boolean isConnected() {
        return connected;
    }

    public String getDbname() {
        return dbname;
    }

    public String getPass() {
        return pass;
    }

    public String getPort() {
        return port;
    }

    public String getUname() {
        return uname;
    }
}
